In a database management system, SQL statements are used to manipulate data and to retrieve data that matches certain selection criteria. A SQL statement is compiled in memory before being executed by a database engine. Though the compiled form of the SQL statement may be cached in memory for some amount of time for repeated executions, it is eventually discarded. Therefore SQL statements can be considered transient objects in a database system.
In practice, the set of SQL statements used by an application are repeatedly executed, and the same SQL statements can be frequently compiled into memory and executed. Also, some statements, when executed, place a high load on the system by consuming a large amount of system resources, which causes the system's performance to suffer. If knowledge about the frequency or load demands of certain SQL statements is available, then this knowledge can be used to tune these statements by adding controls to improve the performance of these SQL statements.
However, obtaining knowledge about the frequency or load demands for these statements is complex and sometimes impossible. For example, the number of SQL statements that are executed by a database system can be very large and thus very difficult to manage. Conventional database systems typically do not persistently store executed statements and performance information about each statement. The conventional systems are therefore unable to provide a sufficient amount of performance information about the executed statements to identify which statements are consuming a substantial amount of resources. Also, conventional database systems are unable to allow a database administrator (DBA) to filter or rank the executed statements based on their performance characteristics.
A need exists for a method to persistently store SQL statements and their performance information into a database as an object that can be used in a performance analysis such as a comparison of system performance between different time periods. Also, a need exists to identify high load SQL statements.